package DAO;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import DBUtility.DBUtility;
import ValueObject.DirectorateVO;

/**
 * @author VietCTT
 */
public class DirectorateDAOImp implements DirectorateDAO {
    /**
     * Variable of connection.
     */
    private Connection con;
    /**
     * Variable of CallableStatement.
     */
    private CallableStatement cstm;
    /**
     * Variable of ResultSet.
     */
    private ResultSet rs;
    /**
     * Return of executeUpdate.
     */
    private int result;

    /**
     * Get Directorate List.
     * @param key
     * @return list of directorates
     * @throws ClassNotFoundException
     * @throws SQLException
     * @see mock.appcode.dao.daointerface.DirectorateDAO#getList(java.lang.String)
     */
    @Override
    public ArrayList<DirectorateVO> getList(String key) throws ClassNotFoundException, SQLException {
        ArrayList<DirectorateVO> listDirect = new ArrayList<DirectorateVO>();
        DirectorateVO directVO = null;
        try {
            // Connect to database
            con = DBUtility.makeConnection();
            // Call procedure
            cstm = con.prepareCall("{call dbo.P_directList(?)}");
            // Input key parameter to call store procedure
            cstm.setString(1, key);
            // Execute query
            rs = cstm.executeQuery();
            // Assign data to directVO from ResultSet
            while (rs.next()) {
                directVO = new DirectorateVO(rs.getNString("Name"), rs.getNString("AddressLine1"),
                        rs.getNString("PostCode"), rs.getNString("countyName"), rs.getBoolean("IsActive"));
                listDirect.add(directVO);
                return listDirect;
            }
        } catch (Exception ex) {
        } finally {
            con.close();
        }
        return listDirect;
    }
    /**
     * View detail of an object Directorate
     * @param id
     * @return object of directorate
     * @throws ClassNotFoundException
     * @throws SQLException
     * @see mock.appcode.dao.daointerface.DirectorateDAO#getDirect(java.lang.String)
     */
    @Override
    public DirectorateVO getDirect(String id) throws ClassNotFoundException, SQLException {
        DirectorateVO directVO = null;
        try {
            // Connect to database
            con = DBUtility.makeConnection();
            // Call store procedure
            cstm = con.prepareCall("{call dbo.P_directDetails(?)}");
            // Input id parameter to call store procedure
            cstm.setString(1, id);
            // Execute query
            rs = cstm.executeQuery();
            // Assign rs to directVO
            if (rs.next()) {
                directVO = new DirectorateVO(rs.getNString("Name"), rs.getNString("shortDescription"),
                        rs.getNString("LeadContactID"), rs.getNString("AddressLine1"), rs.getNString("AddressLine2"),
                        rs.getNString("AddressLine3"), rs.getNString("PostCode"), rs.getNString("townName"),
                        rs.getNString("countyName"), rs.getNString("BusinessName"), rs.getInt("SICCode"),
                        rs.getNString("FullDescription"), rs.getInt("PhoneNumber"), rs.getInt("Fax"),
                        rs.getNString("Email"), rs.getNString("WebAddress"), rs.getNString("CharityNumber"),
                        rs.getNString("CompanyNumber"), rs.getBoolean("IsActive"));
            }
        } catch (Exception ex) {
        }finally{
            con.close();
        }
        return directVO;
    }
    @Override
    public boolean updateDirect(DirectorateVO directorate) throws ClassNotFoundException, SQLException {
        // TODO Auto-generated method stub
        return false;
    }
    /**
     * Change status of Directorate
     * @param id of directorate
     * @param status
     * @return true if change successfully, otherwise return false
     * @throws ClassNotFoundException
     * @throws SQLException
     * @see mock.appcode.dao.daointerface.DirectorateDAO#activeStatus(int, boolean)
     */
    @Override
    public boolean activeStatus(int id, boolean status) throws ClassNotFoundException, SQLException {
        String sql = "";
        CallableStatement cstm = null;
        sql = "{call dbo.P_changeStatus(?,?)}";

        try {
            // Connect to database
            con = DBUtility.makeConnection();
            // prepared statement
            cstm = con.prepareCall(sql);
            // set id and status parameter
            cstm.setInt(1, id);
            cstm.setBoolean(2, status);
            // execute query
            int row = cstm.executeUpdate();
        } catch (SQLException ex) {
            throw ex;
        } finally {
            try {
                con.close();
            } catch (SQLException ex2) {
                throw ex2;
            }
        }
        if (result == 1)
            return true;
        else
            return false;
    }

}
